package scantask.server.dataLayer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Set;
import java.util.Vector;

import scantask.server.dataLayer.exceptions.AddAreaException;
import scantask.server.dataLayer.exceptions.AddBenchmarkException;
import scantask.server.dataLayer.exceptions.AddMeteorologyException;
import scantask.server.dataLayer.exceptions.AddMoistureException;
import scantask.server.dataLayer.exceptions.AddPestException;
import scantask.server.dataLayer.exceptions.AddSyntheticObjectException;
import scantask.server.dataLayer.exceptions.AddTemperatureException;
import scantask.server.dataLayer.exceptions.AddUserException;
import scantask.server.dataLayer.exceptions.GetAreaException;
import scantask.server.dataLayer.exceptions.GetBenchmarksException;
import scantask.server.dataLayer.exceptions.GetDefaultLocationException;
import scantask.server.dataLayer.exceptions.GetMoistureException;
import scantask.server.dataLayer.exceptions.GetPasswordException;
import scantask.server.dataLayer.exceptions.GetPestException;
import scantask.server.dataLayer.exceptions.GetSyntheticObjectsException;
import scantask.server.dataLayer.exceptions.GetTemperatureException;
import scantask.server.dataLayer.exceptions.GetUserException;
import scantask.server.dataLayer.exceptions.GetValidDatesException;
import scantask.server.dataLayer.exceptions.GetVertexesException;

import scantask.containers.Area;
import scantask.containers.Benchmark;
import scantask.containers.Coordinate;
import scantask.containers.Moisture;
import scantask.containers.Pest;
import scantask.containers.SyntheticObject;
import scantask.containers.Temperature;
import scantask.containers.User;
import scantask.containers.WeekDate;
import scantask.containers.ZoomLevel;
import scantask.containers.exceptions.IllegalAreaException;
import scantask.containers.exceptions.IllegalBenchmarkNameException;
import scantask.containers.exceptions.IllegalLocationNameExeption;
import scantask.containers.exceptions.IllegalPasswordException;
import scantask.containers.exceptions.IllegalUserNameException;
import scantask.containers.exceptions.IllegaleRadiusException;
import scantask.containers.exceptions.InvalidCoordinateValueException;
import scantask.containers.exceptions.InvalidMeasurementTypeException;
import scantask.containers.exceptions.InvalidSyntheticObjectTypeException;
import scantask.containers.exceptions.InvalidWeekNumberException;
import scantask.containers.exceptions.InvalidYearException;
import scantask.containers.exceptions.InvalidZoomLevelParametersException;
import scantask.containers.exceptions.UnInitializeObjectException;

public class SQLManipulator implements DataManipulatorInterface {

	private final static String MYSQL_DRIVER = "com.mysql.jdbc.Driver";
	private final static String MYSQL_URL_HOST = "jdbc:mysql://";
	private final static String HOST = "localhost";
	private final static String PORT = "3306";
	private final static String DB_NAME = "scantask3D";
	private final static String USER_NAME = "administrator";
	private final static String PASSWORD = "scantask";
	
	
	private final static int MAX_USER_NAME_LENGTH = 20;
	private final static int MAX_PASSWORD_LENGTH = 128;
	private final static int MAX_FULL_NAME_LENGTH = 30;
	private final static int MAX_LOCATION_NAME_LENGTH = 50;
	private final static int MAX_SYNTHETIC_TYPE_LENGTH = 50;
//	private final static int MAX_CROP_TYPE_LENGTH = 50;
	private final static int MAX_PEST_TYPE_LENGTH = 50;
	private final static int MAX_METEOROLOGY_STATION_NAME_LENGTH = 50;
	private final static int MAX_AREA_NAME_LENGTH = 50;
	private final static int MAX_AREA_TYPE_NAME_LENGTH = 30;
	private final static int MAX_COLOR_NAME_LENGTH = 20; 
	
	
	private Connection _connection;
	
	private Integer _maxAreaID;
	
	public SQLManipulator() {
		String url = MYSQL_URL_HOST + HOST + ":" + PORT + "/" + DB_NAME;  
		try {
			Class.forName(MYSQL_DRIVER);
			this._connection = DriverManager.getConnection(url, USER_NAME, PASSWORD);
		} catch (ClassNotFoundException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().severe("SQL failed - class not found when performing class.forName");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().severe("SQL failed - set connection failed");
		}
		
		try {
			this.createDatabaseTables();
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create statement");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}
		
		try {
			this._maxAreaID = this.getMaxAreaIDFromAreasTable();
		} catch (SQLException e) {
			this._maxAreaID = new Integer(0);
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed get max area ID, set it to ZERO");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}
		
	}
	
	
	private void createDatabaseTables() throws SQLException {
		Statement statement = this._connection.createStatement();
		
		this.createUsersTable(statement);
		this.createBenchmarksTable(statement);
		this.createSyntheticObjectsTable(statement);
		this.createMeteorologyTable(statement);
		this.createTemperaturesTable(statement);
		this.createMoisturesTable(statement);
		this.createAreaTable(statement);
		this.createAreaVertexesTable(statement);
//		this.createCropsTable(statement);
		this.createPestsTable(statement);
		
		statement.close();
	}
	
	private void createUsersTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE Users(UserName char(" + MAX_USER_NAME_LENGTH + "), " +
				"Password char(" + MAX_PASSWORD_LENGTH + ") NOT NULL," + 
				"FullName char(" + MAX_FULL_NAME_LENGTH + ")," +
				"DefaultX double, DefaultY double," +
				"PRIMARY KEY(UserName))");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table Users");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}
	}
	
	private void createBenchmarksTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE Benchmarks(UserName char(" + MAX_USER_NAME_LENGTH + ") REFERENCES Users(UserName), " +
					"X double NOT NULL," + 
					"Y double NOT NULL," +
					"LocationName char(" + MAX_LOCATION_NAME_LENGTH + ")," +
					"PRIMARY KEY(UserName, LocationName))");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table Benchmarks");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}
	}
	
	private void createSyntheticObjectsTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE SyntheticObjects(UserName char(" + MAX_USER_NAME_LENGTH + ") REFERENCES Users(UserName), " +
					"CenterX double," + 
					"CenterY double," +
					"Radius int," +
					"Type char(" + MAX_SYNTHETIC_TYPE_LENGTH + ") NOT NULL," +
					"MinView int," +
					"MaxView int," +
					"PRIMARY KEY(UserName, CenterX, CenterY))");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table SyntheticObjects");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}	
	}
	
	private void createMeteorologyTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE Meteorology(Name char(" + MAX_METEOROLOGY_STATION_NAME_LENGTH + "), " +
					"X double," +
					"Y double," +
					"PRIMARY KEY(Name))");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table createMeteorologyTable");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}
	}
	
	private void createTemperaturesTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE Temperatures(UserName char(" + MAX_USER_NAME_LENGTH + ") REFERENCES Users(UserName), " +
					"WeekNum int," + 
					"Year int," +
					"Meteorology char(" + MAX_METEOROLOGY_STATION_NAME_LENGTH + ") REFERENCES Meteorology(Name)," +
					"Value double)");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table Temperature");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}	
	}
	
	private void createMoisturesTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE Moistures(UserName char(" + MAX_USER_NAME_LENGTH + ") REFERENCES Users(UserName), " +
					"WeekNum int," + 
					"Year int," +
					"Meteorology char(" + MAX_METEOROLOGY_STATION_NAME_LENGTH + ") REFERENCES Meteorology(Name)," +
					"Value double)");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table Moisture");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}	
	}
	
	private void createAreaTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE Areas(ID int NOT NULL," +
					"UserName char(" + MAX_USER_NAME_LENGTH + ") REFERENCES Users(UserName), " +
					"CenterX double NOT NULL," +
					"CenterY double NOT NULL," +
					"Name char(" + MAX_AREA_NAME_LENGTH + ")," +
					"Type char(" + MAX_AREA_TYPE_NAME_LENGTH + ")," +
					"Color char(" + MAX_COLOR_NAME_LENGTH + ") NOT NULL," +
					"PRIMARY KEY(UserName, Name))");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table Area");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}
	}
	
	private void createAreaVertexesTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE AreaVertexes(ID int NOT NULL," +
					"Location int NOT NULL," +
					"X double NOT NULL," +
					"Y double NOT NULL)");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table AreaVertixes");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}
	}
	
/*	private void createCropsTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE Crops(UserName char(" + MAX_USER_NAME_LENGTH + ") REFERENCES Users(UserName), " +
					"Type char(" + MAX_CROP_TYPE_LENGTH + ") NOT NULL," +
					"WeekNum int," + 
					"Year int," +
					"X double," +
					"Y double," +
					"Value double," +
					"PRIMARY KEY(UserName, WeekNum, Year, X, Y))");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table Users");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}	
	}
*/	
	private void createPestsTable(Statement statement) {
		try {
			statement.executeUpdate("CREATE TABLE Pests(UserName char(" + MAX_USER_NAME_LENGTH + ") REFERENCES Users(UserName), " +
					"Type char(" + MAX_PEST_TYPE_LENGTH + ") NOT NULL," +
					"WeekNum int," + 
					"Year int," +
					"AreaName char(" + MAX_AREA_NAME_LENGTH + ") REFERENCES Areas(Name)," +
					"Value double, IsInterpolated Boolean)");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().info("SQL failed create table Pests");
			scantask.server.utilities.logging.SingletonLogger.getLogger().info(e.getMessage());
		}	
	}
	
	private Integer getMaxAreaIDFromAreasTable() throws SQLException {
		Statement statement = null;
		ResultSet rs = null;
		Integer ans = 0;
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get max ID because it fail create statement");
			throw e;
		}
		try {
			rs = statement.executeQuery("SELECT MAX(ID) FROM Areas");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw e;		
		}
		try {
			if (rs.next()) {
				ans = rs.getInt(1);
			}
			else {
				ans = new Integer(0);
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw e;
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		}
		
		return ans;
	}
	
	
	
	@Override
	public void addBenchmark(String userName, Benchmark benchmark) throws AddBenchmarkException {
		double x;
		double y;
		String locationName;
		x = benchmark.getCoordinate().getX();
		y = benchmark.getCoordinate().getY();
		locationName = benchmark.getName();
		
		String query = "INSERT INTO Benchmarks(UserName, X, Y, LocationName)" +
				" VALUES('" + userName + "', " + x + ", " + 
				y + ", '" + locationName + "')";
		try {
			this.addQuery(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new benchmark" + benchmark.getName());
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddBenchmarkException("Error - add new benchmark failed");
		}
		
	}

	@Override
	public Vector<Benchmark> getBenchmarks(String userName) throws GetBenchmarksException {
		Statement statement = null;
		ResultSet rs = null;
		Vector<Benchmark> benchmarks = new Vector<Benchmark>();
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get benchmarks because it fail create statement");
			throw new GetBenchmarksException("Error - get benchmarks failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT X, Y, LocationName FROM Benchmarks WHERE UserName = '" + userName + "'");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetBenchmarksException("Error - get benchmarks failed");		
		}
		try {
			while (rs.next()) {
				Coordinate coordinate;
				try {
					coordinate = new Coordinate(rs.getDouble("X"), rs.getDouble("y"));
					benchmarks.add(new Benchmark(rs.getString("LocationName"), coordinate));
				} catch (InvalidCoordinateValueException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed initialize legal coordinate for benchmark");
					continue;
				} catch (IllegalBenchmarkNameException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed initialize legal benchmark, illegal benchmark name");
					continue;
				} catch (UnInitializeObjectException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed initialize legal coordinate for benchmark, coordinate is null");
					continue;
				} 
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetBenchmarksException("Error - get benchmarks failed");
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return benchmarks;
		}
		
		return benchmarks;
	}

/*	
	@Override
	public Vector<Crop> getCrop(String userName, WeekDate date) throws GetCropException {
		if (date == null) {
			throw new GetCropException();
		}
		Statement statement = null;
		ResultSet rs = null;
		Double x = null;
		Double y = null;
		Double value;
		String type;
		
		Vector<Crop> crops = new Vector<Crop>();
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get crops because it fail create statement");
			throw new GetCropException("Error - Get Crops failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT Type, X, Y, Value FROM Crops WHERE UserName = '" + userName + "' AND WeekNum = " + date.getWeek() + " AND Year = " + date.getYear() + " ORDER BY Type");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetCropException("Error - Get Crops failed");		
		}
		try {
			while (rs.next()) {
				Crop crop = null;
				type = rs.getString("Type");
				x = rs.getDouble("X");
				y = rs.getDouble("Y");
				if ((x == null) || (y == null)) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed X or Y value of crop is null");
					continue;
				}
				value = rs.getDouble("Value");
				boolean interpolate = false;
				if (value == null) {
					value = new Double(0);
					interpolate = true;
				}
				try {
					crop = new Crop(new Coordinate(x.doubleValue(), y.doubleValue()), null, value.doubleValue(), date, interpolate, type);
				} catch (UnInitializeObjectException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add crop, uninitialize coordinate");
					continue;
				} catch (InvalidMeasurementTypeException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add crop, invalid measurement type " + type);
					continue;
				} catch (InvalidCoordinateValueException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add crop, invalid coordinate value");
					continue;
				}
				crops.add(crop);
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetCropException("Error - Get Crops failed");
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return crops;
		}
		
		return crops;
	}
*/
	@Override
	public Coordinate getDefaultLocation(String userName) throws GetDefaultLocationException{
		Statement statement = null;
		ResultSet rs = null;
		Double x;
		Double y;
		Coordinate coordinate;
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get user default location because it fail create statement");
			throw new GetDefaultLocationException("Error - Get default location failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT DefaultX, DefaultY FROM Users WHERE UserName = '" + userName + "'");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetDefaultLocationException("Error - Get default location failed");		
		}
		try {
			if (rs.next()) {
				x = rs.getDouble("DefaultX");
				y = rs.getDouble("DefaultY");
				if ((x == null) || (y == null)) {
					try {
						statement.close();
					} catch (SQLException e1) {
						scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
					}
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed user doesn't have default location");
					throw new GetDefaultLocationException("Error - User does not have default location coordinate");
				}
				try {
					coordinate = new Coordinate(x.doubleValue(), y.doubleValue());
				} catch (InvalidCoordinateValueException e) {
					try {
						statement.close();
					} catch (SQLException e1) {
						scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
					}
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed initialize new Coordinate");
					throw new GetDefaultLocationException("Error - User default location is illegal coordinate, please contact administrator");
				}
			}
			else {
				try {
					statement.close();
				} catch (SQLException e1) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
				}
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed user does not exist");
				throw new GetDefaultLocationException("Error - User does not exist");
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetDefaultLocationException("Error - Get default location failed");
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return coordinate;
		}
		
		return coordinate;

	}

	@Override
	public HashMap<String,Vector<Moisture>> getMoisture(String userName, WeekDate date) throws GetMoistureException {
		if (date == null) {
			throw new GetMoistureException();
		}
		Statement statement = null;
		Statement statement2 = null; 
		ResultSet rs = null;
		ResultSet rsMeteorology = null;
		Double x = null;
		Double y = null;
		String meteorology = "";
		Double value;
		
		HashMap<String, Vector<Moisture>> moistures = new HashMap<String, Vector<Moisture>>();
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get moistures because it fail create statement");
			throw new GetMoistureException("Error - Get moistures failed");
		}
		try {
			statement2 = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get moistures because it fail create statement");
			throw new GetMoistureException("Error - Get moistures failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT Meteorology, Value FROM Moistures WHERE UserName = '" + userName + "' AND WeekNum = " + date.getWeek() + " AND Year = " + date.getYear());
		} catch (SQLException e) {
			try {
				statement.close();
				statement2.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetMoistureException("Error - Get moistures failed");		
		}
		try {
			while (rs.next()) {
				meteorology = "";
				meteorology = rs.getString("Meteorology");
				if (moistures.containsKey(meteorology) == false) {
					moistures.put(meteorology, new Vector<Moisture>());
				}
				rsMeteorology = statement2.executeQuery("SELECT X, Y FROM Meteorology WHERE Name = '" + meteorology + "'");
				if (rsMeteorology.next()) {
					x = rsMeteorology.getDouble("X");
					y = rsMeteorology.getDouble("Y");
				}
				else {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get meteorology station values");
					continue;
				}
				Moisture moisture = null;
				if ((x == null) || (y == null)) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed X or Y value of moisture is null");
					continue;
				}
				value = rs.getDouble("Value");
				if (value == null) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed Value value of moisture is null");
					continue;
				}
				try {
					moisture = new Moisture(new Coordinate(x.doubleValue(), y.doubleValue()), null, value.doubleValue(), date, meteorology);
				} catch (UnInitializeObjectException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add moisture, uninitialize coordinate");
					continue;
				} catch (InvalidCoordinateValueException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add moisture, invalid coordinate values");
					continue;
				} catch (IllegalLocationNameExeption e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add moisture, invalid location name");
					continue;
				}
				moistures.get(meteorology).add(moisture);
			}
		} catch (SQLException e) {
			try {
				statement.close();
				statement2.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetMoistureException("Error - Get moistures failed");
		}
		try {
			statement.close();
			statement2.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return moistures;
		}
		
		return moistures;
	}

	@Override
	public String getPassword(String userName) throws GetPasswordException {
		Statement statement = null;
		ResultSet rs = null;
		String password = "";
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get user password because it fail create statement");
			throw new GetPasswordException("Error - Get password failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT Password FROM Users WHERE UserName = '" + userName + "'");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetPasswordException("Error - Get password failed");		
		}
		try {
			if (rs.next()) {
				password = rs.getString("Password");
			}
			else {
				try {
					statement.close();
				} catch (SQLException e1) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
				}
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed user does not exist");
				throw new GetPasswordException("Error - User does not exist");
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetPasswordException("Error - Get password failed");
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return password;
		}
		
		return password;
	}
	
	@Override
	public User getUser(String userName) throws GetUserException {
		Statement statement = null;
		ResultSet rs = null;
		String password = "";
		String fullName = "";
		Double x;
		Double y;
		Coordinate coordinate = null;
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get user because it fail create statement");
			throw new GetUserException("Error - Get user failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT Password, FullName, DefaultX, DefaultY FROM Users WHERE UserName = '" + userName + "'");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetUserException("Error - Get user failed");		
		}
		try {
			if (rs.next()) {
				password = rs.getString("Password");
				fullName = rs.getString("FullName");
				x = rs.getDouble("DefaultX");
				y = rs.getDouble("DefaultY");
				if ((x != null) && (y != null)) {
					try {
						coordinate = new Coordinate(x.doubleValue(), y.doubleValue());
					} catch (InvalidCoordinateValueException e) {
						scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed initialize new Coordinate, coordinate = null for user: " + userName);
						coordinate = null;
					}
				}
			}
			else {
				try {
					statement.close();
				} catch (SQLException e1) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
				}
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed user does not exist");
				throw new GetUserException("Error - User does not exist");
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetUserException("Error - Get user failed");
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			try {
				return new User(userName, password, fullName, coordinate);
			} catch (IllegalUserNameException e) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("failed initialize user " + userName);
				throw new GetUserException("Error - Illegal user name, Please contact administrator");
			} catch (IllegalPasswordException e) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("failed initialize user " + userName);
				throw new GetUserException("Error - Illegal password, Please contact administrator");
			}
		}
		
		try {
			return new User(userName, password, fullName, coordinate);
		} catch (IllegalUserNameException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("failed initialize user " + userName);
			throw new GetUserException("Error - Illegal user name, Please contact administrator");
		} catch (IllegalPasswordException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("failed initialize user " + userName);
			throw new GetUserException("Error - Illegal password, Please contact administrator");
		}

	}

	@Override
	public Vector<Pest> getPest(String userName, WeekDate date) throws GetPestException {
		if (date == null) {
			throw new GetPestException();
		}
		Statement statement = null;
		ResultSet rs = null;
		Double value;
		String type;
		String areaName;
		Boolean interpolated = false;
		
		Vector<Pest> pests = new Vector<Pest>();
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get pests because it fail create statement");
			throw new GetPestException("Error - Get Pests failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT Type, AreaName, Value, IsInterpolated FROM Pests WHERE UserName = '" + userName + "' AND WeekNum = " + date.getWeek() + " AND Year = " + date.getYear());
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetPestException("Error - Get Pests failed");		
		}
		try {
			while (rs.next()) {
				Pest pest = null;
				type = rs.getString("Type");
				areaName = rs.getString("AreaName");
				value = rs.getDouble("Value");
				interpolated = rs.getBoolean("IsInterpolated");
				boolean interpolate = false;
				if ((value == null) || (interpolated == true)) {
					value = new Double(0);
					interpolate = true;
				}
				try {
					// the coordinate is (0,0) because it uninitialized yet shall update according to area 
					pest = new Pest(new Coordinate(0,0), null, value.doubleValue(), date, interpolate, type, areaName);
				} catch (UnInitializeObjectException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add pest, uninitialize coordinate");
					continue;
				} catch (InvalidMeasurementTypeException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add pest, invalid measurement type " + type);
					continue;
				} catch (InvalidCoordinateValueException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add pest, invalid coordinate value");
					continue;
				} catch (IllegalLocationNameExeption e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add pest, invalid location name");
					continue;
				}
				pests.add(pest);
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetPestException("Error - Get Pests failed");
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return pests;
		}
		
		return pests;
	}

	@Override
	public Vector<SyntheticObject> getSynthetices(String userName) throws GetSyntheticObjectsException {
		Statement statement = null;
		ResultSet rs = null;
		Vector<SyntheticObject> synthetics = new Vector<SyntheticObject>();
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get synthetic objects because it fail create statement");
			throw new GetSyntheticObjectsException("Error - Get synthetic objects failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT CenterX, CenterY, Radius, Type, MinView, MaxView FROM SyntheticObjects WHERE UserName = '" + userName + "'");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetSyntheticObjectsException("Error - Get synthetic objects failed");		
		}
		try {
			while (rs.next()) {
				Coordinate coordinate;
				Integer minView;
				Integer maxView;
				ZoomLevel zoomLevel = null;
				String type;
				Integer radius;
				try {
					coordinate = new Coordinate(rs.getDouble("CenterX"), rs.getDouble("CenterY"));
				} catch (InvalidCoordinateValueException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed invalid coordinate exception for getSynthetics");
					continue;
				}
				minView = rs.getInt("MinView");
				maxView = rs.getInt("MaxView");
				if ((minView != null) && (maxView != null)) {
					try {
						zoomLevel = new ZoomLevel(minView.intValue(), maxView.intValue());
					} catch (InvalidZoomLevelParametersException e) {
						scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed invalid zoomLevel exception for getSynthetics");
						continue;
					}
				}
				type = rs.getString("Type");
				radius = rs.getInt("Radius");
				if (radius == null) {
					radius = new Integer(1);
				}
				try {
					synthetics.add(new SyntheticObject(coordinate, zoomLevel, type, radius));
				} catch (UnInitializeObjectException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed exception for getSynthetics");
				} catch (IllegaleRadiusException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed exception for getSynthetics");
				} catch (InvalidSyntheticObjectTypeException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed exception for getSynthetics");
				} 
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetSyntheticObjectsException("Error - Get synthetic objects failed");
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return synthetics;
		}
		
		return synthetics;
	}

	@Override
	public HashMap<String,Vector<Temperature>> getTemperature(String userName, WeekDate date) throws GetTemperatureException {
		if (date == null) {
			throw new GetTemperatureException();
		}
		Statement statement = null;
		Statement statement2 = null;
		ResultSet rs = null;
		ResultSet rsMeteorology = null;
		Double x = null;
		Double y = null;
		Double value;
		String meteorology = "";
		
		HashMap<String,Vector<Temperature>> temperatures = new HashMap<String, Vector<Temperature>>();
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get temperatures because it fail create statement");
			throw new GetTemperatureException("Error - Get Temperatures failed");
		}
		try {
			statement2 = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get temperatures because it fail create statement");
			throw new GetTemperatureException("Error - Get Temperatures failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT Meteorology, Value FROM Temperatures WHERE UserName = '" + userName + "' AND WeekNum = " + date.getWeek() + " AND Year = " + date.getYear());
		} catch (SQLException e) {
			try {
				statement.close();
				statement2.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetTemperatureException("Error - Get Temperatures failed");		
		}
		try {
			while (rs.next()) {
				meteorology = "";
				meteorology = rs.getString("Meteorology");
				if (temperatures.containsKey(meteorology) == false) {
					temperatures.put(meteorology, new Vector<Temperature>());
				}
				rsMeteorology = statement2.executeQuery("SELECT X, Y FROM Meteorology WHERE Name = '" + meteorology + "'");
				if (rsMeteorology.next()) {
					x = rsMeteorology.getDouble("X");
					y = rsMeteorology.getDouble("Y");
				}
				else {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get meteorology station values");
					continue;
				}
				Temperature temp = null;
				if ((x == null) || (y == null)) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed X or Y value of temperature is null");
					continue;
				}
				value = rs.getDouble("Value");
				if (value == null) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed Value value of temperature is null");
					continue;
				}
				try {
					temp = new Temperature(new Coordinate(x.doubleValue(), y.doubleValue()), null, value.doubleValue(), date, meteorology);
				} catch (UnInitializeObjectException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add temperature, uninitialize coordinate");
					continue;
				} catch (InvalidCoordinateValueException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add temperature, invalid coordinate values");
					continue;
				} catch (IllegalLocationNameExeption e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add temperature, invalid location name");
					continue;				}
				temperatures.get(meteorology).add(temp);
			}
		} catch (SQLException e) {
			try {
				statement.close();
				statement2.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetTemperatureException("Error - Get Temperatures failed");
		}
		try {
			statement.close();
			statement2.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return temperatures;
		}
		
		return temperatures;
	}

	@Override
	public Set<WeekDate> getValidDates(String userName) {
		Set<WeekDate> dates = new HashSet<WeekDate>();
		try {
			dates.addAll(this.getDates(userName, "Temperatures"));
		} catch (GetValidDatesException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed while trying to get Temperature WeekDates");
		}
		try {
			dates.addAll(this.getDates(userName, "Moistures"));
		} catch (GetValidDatesException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed while trying to get Moisture WeekDates");
		}
/*		try {
			dates.addAll(this.getDates(userName, "Crops"));
		} catch (GetValidDatesException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed while trying to get Crop WeekDates");
		}*/
		try {
			dates.addAll(this.getDates(userName, "Pests"));
		} catch (GetValidDatesException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed while trying to get Pest WeekDates");
		}
		return dates;
	}
	
	
	@Override
	public Vector<Area> getArea(String userName) throws GetAreaException {
		Statement statement = null;
		ResultSet rs = null;
		Integer id;
		Double xCenter = null;
		Double yCenter = null;
		String name = "";
		String type = "";
		String color = "";
		Vector<Coordinate> vertexes;
		
		Vector<Area> areas = new Vector<Area>();
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get areas because it fail create statement");
			throw new GetAreaException("Error - Get Area failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT ID, CenterX, CenterY, Name, Type, Color FROM Areas WHERE UserName = '" + userName + "'");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetAreaException("Error - Get Areas failed");		
		}
		try {
			while (rs.next()) {
				id = rs.getInt("ID");
				xCenter = rs.getDouble("CenterX");
				yCenter = rs.getDouble("CenterY");
				name = rs.getString("Name");
				type = rs.getString("Type");
				color = rs.getString("Color");
				Area area = null;
				if ((xCenter == null) || (yCenter == null)) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed X-Center or Y-Center value of area is null");
					continue;
				}
				try {
					vertexes = this.getVertexes(id.intValue());
				} catch (GetVertexesException e1) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add area, invalid vertexes exception");
					continue;
				}
				try {
					area = new Area(new Coordinate(xCenter, yCenter), vertexes, name, type, color);
				} catch (IllegalAreaException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add area, invalid area exception");
					continue;
				} catch (InvalidCoordinateValueException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add temperature, invalid coordinate values");
					continue;
				}
				areas.add(area);
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetAreaException("Error - Get Areas failed");
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return areas;
		}
		
		return areas;
	}
	
	private Vector<Coordinate> getVertexes(int id) throws GetVertexesException {
		Statement statement = null;
		ResultSet rs = null;
		Double x = null;
		Double y = null;

		Vector<Coordinate> vertexes = new Vector<Coordinate>();
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get vertexes because it fail create statement");
			throw new GetVertexesException("Error - Get vertexes failed");
		}
		
		try {
			rs = statement.executeQuery("SELECT X, Y FROM AreaVertexes WHERE ID = " + id + " ORDER BY Location");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetVertexesException("Error - Get Area vertexes failed");		
		}
		try {
			while (rs.next()) {
				x = rs.getDouble("X");
				y = rs.getDouble("Y");
				Coordinate coordinate = null;
				if ((x == null) || (y == null)) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed X or Y value of area vetexes is null");
					continue;
				}
				try {
					coordinate = new Coordinate(x.doubleValue(), y.doubleValue());
				} catch (InvalidCoordinateValueException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add vertex, invalid coordinate value exception");
					continue;
				}
				vertexes.add(coordinate);
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetVertexesException("Error - Get vertexes failed");
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return vertexes;
		}
		
		return vertexes;
	}
	
	
	private Set<WeekDate> getDates(String userName, String tableName) throws GetValidDatesException{
		Statement statement = null;
		ResultSet rs = null;
		Set<WeekDate> dates = new HashSet<WeekDate>();
		try {
			statement = this._connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed get weekDates because it fail create statement");
			throw new GetValidDatesException();
		}
		
		try {
			rs = statement.executeQuery("SELECT WeekNum, Year FROM " + tableName + " WHERE UserName = '" + userName + "'");
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform query");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetValidDatesException();		
		}
		try {
			while (rs.next()) {
				WeekDate date;
				try {
					date = new WeekDate(rs.getInt("WeekNum"), rs.getInt("Year"));
					dates.add(date);
				} catch (InvalidWeekNumberException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed create new Week Date illegal week number");
				} catch (InvalidYearException e) {
					scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed create new Week Date illegal year");
				}
			}
		} catch (SQLException e) {
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
			}
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed use ResultSet");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new GetValidDatesException();
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		
			return dates;
		}
		
		return dates;

	}


/*
	@Override
	public void addCrop(String userName, Crop crop) throws AddCropException {
		String type;
		int week;
		int year;
		double x;
		double y;
		Double value;
		try {
			type = crop.getType();
			week = crop.getDate().getWeek();
			year = crop.getDate().getYear();
			x = crop.getCoordinate().getX();
			y = crop.getCoordinate().getY();
			if (crop.isInterpolated()) {
				value = null;
			}
			else {
				value = new Double(crop.getValue());
			}
		} catch (UnInitializeObjectException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new Crop because obligate parameter does not exist");
			throw new AddCropException();
		}
		
		String query = "INSERT INTO Crops(UserName, Type, WeekNum, Year, " +
		"X, Y, Value) VALUES('" + userName + "', '" + type + "', " + 
		week + ", " + year + ", " +
		x + ", " + y + ", " + value + ")";
		
		try {
			this.addQuery(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new crop");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddCropException();
		}
		
	}
*/


	@Override
	public void addMoisture(String userName, Moisture moisture) throws AddMoistureException {
		int week;
		int year;
		String meteorology = "";
		double value;
		try {
			week = moisture.getDate().getWeek();
			year = moisture.getDate().getYear();
			meteorology = moisture.getLocationName();
			value = moisture.getValue();
		} catch (UnInitializeObjectException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new Moisture because obligate parameter does not exist");
			throw new AddMoistureException();
		}
		
		String query = "INSERT INTO Moistures(UserName, WeekNum, Year, " +
		"Meteorology, Value) VALUES('" + userName + "', " +
		week + ", " + year + ", '" +
		meteorology + "', " + value + ")";
		
		try {
			this.addQuery(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new moisture");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddMoistureException();
		}
	}



	@Override
	public void addPest(String userName, Pest pest) throws AddPestException {
		String type;
		String areaName;
		int week;
		int year;
		Double value;
		try {
			type = pest.getCleanType();
			areaName = pest.getLocationName();
			week = pest.getDate().getWeek();
			year = pest.getDate().getYear();
			if (pest.isInterpolated()) {
				value = null;
			}
			else {
				value = new Double(pest.getValue());
			}
		} catch (UnInitializeObjectException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new Pest because obligate parameter does not exist");
			throw new AddPestException();
		}
		
		String query = "INSERT INTO Pests(UserName, Type, WeekNum, Year, " +
		"AreaName, Value, IsInterpolated) VALUES('" + userName + "', '" + type + "', " + 
		week + ", " + year + ", '" +
		areaName + "', " + value + ", " + pest.isInterpolated() + ")";
		
		try {
			this.addQuery(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new pest");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddPestException();
		}
	
	}



	@Override
	public void addSyntheticObject(String userName, SyntheticObject synthetic) throws AddSyntheticObjectException {
		double centerX;
		double centerY;
		int radius;
		String type;
		Integer minView;
		Integer maxView;
		centerX = synthetic.getCoordinate().getX();
		centerY = synthetic.getCoordinate().getY();
		radius = synthetic.getRadius();
		type = synthetic.getCleanType();
		try {
			minView = synthetic.getViewInterval().getFrom();
		} catch (UnInitializeObjectException e) {
			minView = null;
		}
		try {
			maxView = synthetic.getViewInterval().getTo();
		} catch (UnInitializeObjectException e) {
			maxView = null;
		}
		
		String query = "INSERT INTO SyntheticObjects(UserName, CenterX, CenterY, " +
		"Radius, Type, MinView, MaxView) VALUES('" + userName + "', " +
		centerX + ", " + centerY + ", " +
		radius + ", '" + type + "', " + minView + ", " + maxView + ")";
		
		try {
			this.addQuery(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new SyntheticObject" + synthetic.getCleanType());
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddSyntheticObjectException();
		}
	}



	@Override
	public void addTemperature(String userName, Temperature temp) throws AddTemperatureException {
		int week;
		int year;
		String meteorology = "";
		Double value;
		try {
			week = temp.getDate().getWeek();
			year = temp.getDate().getYear();
			meteorology = temp.getLocationName();
			value = new Double(temp.getValue());
		} catch (UnInitializeObjectException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new Temerature because obligate parameter does not exist");
			throw new AddTemperatureException();
		}
		
		String query = "INSERT INTO Temperatures(UserName, WeekNum, Year, " +
		"Meteorology, Value) VALUES('" + userName + "', " +
		week + ", " + year + ", '" +
		meteorology + "', " + value + ")";
		
		try {
			this.addQuery(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new temperature");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddTemperatureException();
		}
	
	}



	@Override
	public void addUser(User user) throws AddUserException {
		double defaultX;
		double defaultY;
		try {
			defaultX = user.getDefaultCoordinate().getX();
			defaultY = user.getDefaultCoordinate().getY();
		} catch (UnInitializeObjectException e1) {
			defaultX = 0;
			defaultY = 0;
		}
		String query = "INSERT INTO Users(UserName, Password, FullName, " +
		"DefaultX, DefaultY) VALUES('" + user.getUserName() + "', '" +
		user.getPassword() + "', '" + user.getFullName() + "', " +
		defaultX + ", " + defaultY + ")";

		try {
			this.addQuery(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new user " + user.getUserName());
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddUserException();
		}
		
	}
	
	
	@Override
	public void addArea(String userName, Area area) throws AddAreaException {
		int id = this.getNextAreaID(); 
		double centerX = area.getCenterCoordinate().getX();
		double centerY = area.getCenterCoordinate().getY();
		String name = area.getName();
		String type = area.getType();
		String color = area.getColorStr();
		Vector<Coordinate> vertexes = null;
		try {
			vertexes = area.getVertixes();
		} catch (UnInitializeObjectException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new area of" + userName + " (" + name + ") due to illegal vertexes");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddAreaException();
		}
		String query = "INSERT INTO Areas(ID, UserName, CenterX, CenterY," +
		"Name, Type, Color) VALUES(" + id + ", '" +
		userName + "', " + centerX + ", " + centerY + ", '" +
		name + "', '" + type + "', '" + color + "')";

		try {
			this.addQuery(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new area of" + userName + " (" + name + ")");
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddAreaException();
		}
		
		this.addAreaVertexes(id, vertexes);

	}
	
	
	private void addAreaVertexes(int id, Vector<Coordinate> vertexes) { 
		double centerX = 0;
		double centerY = 0;
		for (int location = 0; location < vertexes.size(); location ++) {
			centerX = vertexes.get(location).getX();
			centerY = vertexes.get(location).getY();
			String query = "INSERT INTO AreaVertexes(ID, Location, X, Y) " +
			"VALUES(" + id + ", " + location + ", " +
			centerX + ", " + centerY + ")";

			try {
				this.addQuery(query);
			} catch (SQLException e) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new vertex of area ID " + id + " keep trying add other vertexes of the area");
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			}
			
		}
	}


	@Override
	public void addMeteorologyStation(String name, double x, double y) throws AddMeteorologyException {
		String query = "INSERT INTO Meteorology(Name, X, Y) " +
		"VALUES('" + name + "', " + x + ", " + y + ")";

		try {
			this.addQuery(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed add new mateorology " + name);
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw new AddMeteorologyException();
		}
		
	}
	
	private int getNextAreaID() {
		synchronized (this._maxAreaID) {
			this._maxAreaID ++;
		}
		return this._maxAreaID.intValue();
	}

	
	private void addQuery(String query) throws SQLException {
		Statement statement = null;
		try {
			statement = this._connection.createStatement();
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform " + query);
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			throw e;
		}
		try {
			statement.executeUpdate(query);
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed performed " + query);
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e.getMessage());
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed perform " + query);
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning(e1.getMessage());
				throw e1;
			}
			throw e;
		}
		try {
			statement.close();
		} catch (SQLException e1) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed close statement");
		}
		
	}


	@Override
	public void clearDataBase() {
		Statement statement = null;
		try {
			statement = this._connection.createStatement();
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed clear data base");
		}
		try {
			statement.executeUpdate("DROP TABLE Pests");
//			statement.executeUpdate("DROP TABLE Crops");
			statement.executeUpdate("DROP TABLE Areas");
			statement.executeUpdate("DROP TABLE AreaVertexes");
			statement.executeUpdate("DROP TABLE Moistures");
			statement.executeUpdate("DROP TABLE Temperatures");
			statement.executeUpdate("DROP TABLE Meteorology");
			statement.executeUpdate("DROP TABLE SyntheticObjects");
			statement.executeUpdate("DROP TABLE Benchmarks");
			statement.executeUpdate("DROP TABLE Users");
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed clear data base");
			try {
				statement.close();
			} catch (SQLException e1) {
				scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed clear data base");
			}
		}
		try {
			statement.close();
		} catch (SQLException e) {
			scantask.server.utilities.logging.SingletonLogger.getLogger().warning("SQL failed clear data base");
		}
	}

}
